/**
 * @Title: Tbg.java
 * @Package tbg 
 * @Description: TODO
 * @author tbc
 * @date 2016年6月9日 上午1:19:01
 * @version
 */
package tbg;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @author tbc
 * @version 1.0 create:{2016年6月9日 上午1:19:01}
 * 
 */
public class Tbg {
	static String file = "h:\\Users\\tbc\\Desktop\\南骏.xlsx";
	static XSSFWorkbook wb;
	static XSSFSheet sheet;
	// 数量所在列的序号 - 6406
	static int numIndex = 25;

	public static void main(String[] args) throws Exception {
		init();
		deleteRowByZero();
		write();
	}

	public static void init() throws Exception {
		InputStream in = new FileInputStream(new File(file));
		wb = new XSSFWorkbook(in);
		sheet = wb.getSheetAt(0);
	}

	public static void deleteRowByZero() {
		numIndex = sheet.getRow(0).getLastCellNum() - 1;
		int i = sheet.getLastRowNum() + 1;// 6405
		XSSFRow row;
		while (i > 1) {
			i--;
			row = sheet.getRow(i);
			XSSFCell cell = row.getCell(numIndex);
			System.out.println("...cell raw : " + (cell == null ? "null" : cell.getRawValue()));
			if (cell.getNumericCellValue() == 0.0) {
				// 这个方法可以把startRow至endRow之间的所有行，按照n向上或移动。
				// n如果为正数，则整体区域向下移动，
				// n如果为负数，则整体区域向上移动，
				// 我们如果要删除空行，就可以利用shiftRows方法，从sheet表的最后一行开始向上逆序移动非空行。
				System.out.println("删除第" + i + "行 ");
				// sheet.removeRow(row);
				sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
			}
		}
		System.out.println("deleted...");
	}

	public static XSSFSheet copySheet() {
		XSSFSheet _sheet = wb.createSheet("new sheet");

		for (int i = 0; i <= sheet.getLastRowNum(); i++) {
			XSSFRow row = sheet.getRow(i);
			XSSFRow _row = _sheet.createRow(i);
			for (int j = 0; j < row.getLastCellNum(); j++) {
				XSSFCell cell = row.getCell(j);
				XSSFCell _cell = _row.createCell(j);
				if (cell == null) {
					_cell.setCellValue("");
				} else {
					if (cell.getCellType() == 1) {
						_cell.setCellValue(cell.getStringCellValue());
					} else {
						_cell.setCellValue(cell.getRawValue());
					}
				}
			}
		}

		return _sheet;
	}

	public static void write() throws Exception {
		XSSFSheet _sheet = copySheet();
		wb.removeSheetAt(0);
		OutputStream out = new FileOutputStream(new File(file));
		wb.write(out);
		out.close();
	}
}
